ERROR: ExecEvalAggref
От | eric soroos |
---|---|
Тема | ERROR: ExecEvalAggref |
Дата | |
Msg-id | 90555128.1168357862@[4.42.179.151] обсуждение исходный текст |
Ответы |
Re: ERROR: ExecEvalAggref
|
Список | pgsql-general |
I'm having trouble with an aggregate function under both 7.2.1 and 7.2.3. Essentially, the aggreate function returns the last attribute value seen, so for the example below, profiler(... a ...) = bar and profiler(... b...) = foo. _date a b 1/1/03 foo null 1/2/03 bar baz 1/3/03 null foo For the most part it works properly, except that one of my installations is having trouble. This query fails: (this is a minimal subset of a larger query) update dl_profile set city= profiler(concat(dl_event._eventDate,dl_event.city))::text from dl_event where dl_event._donorNum='385' and dl_profile._donorNum='385' and dl_event._flDeleted='f' ERROR: ExecEvalAggref: no aggregates in this expression context This query succeeds: (same query, different field) update dl_profile set _outgoingSubject= profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text from dl_event where dl_event._donorNum='385' and dl_profile._donorNum='385' and dl_event._flDeleted='f' This query also succeeds, giving the expected values: select profiler(concat(dl_event._eventDate,dl_event.city))::text as city, profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text as outgoingSubject from dl_event where _donorNum='385' and dl_event._flDeleted='f'; city | outgoingsubject -----------+--------------------------------------- Cambridge | ********* News: January 20th, 2003 (1 row) These are the definitions of the functions that the aggregate relies on: CREATE FUNCTION "datefromconcat" (text) RETURNS timestamp with time zone AS 'select substring($1 from 0 for (position(''|'' in $1)-1))::timestamp' LANGUAGE 'sql' WITH ( iscachable, isstrict ); CREATE FUNCTION "valuefromconcat" (text) RETURNS text AS 'select substring($1 from (position(''|'' in $1)+1))' LANGUAGE 'sql' WITH ( iscachable, isstrict ); CREATE FUNCTION "concat" (timestamp with time zone,text) RETURNS text AS 'select $1::text || ''|'' || $2' LANGUAGE 'sql' WITH ( iscachable, isstrict ); CREATE FUNCTION "aggregateprofile" (text,text) RETURNS text AS 'select case when $2 is null then $1 when dateFromConcat($1) > dateFromConcat($2) then $1 else $2 end' LANGUAGE 'sql' WITH ( iscachable, isstrict ); CREATE AGGREGATE profiler ( BASETYPE = text, SFUNC = aggregateprofile, STYPE = text, FINALFUNC = valuefromconcat ); Any ideas?
В списке pgsql-general по дате отправления: